﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
namespace OracleDAL.ElecBoss
{
    public class dutyPlan
    {
        /// <summary>
        /// 获得排班的值班信息
        /// </summary>
        /// <returns></returns>
        public DataSet getDutyPlanUser()
        {
            //            string strSql = "select ID,USERISN,STARTTIME,ENDTIME,DUTYTYPE,DUTYMANAGER from sortduty where sysdate> starttime and to_char(sysdate,'yyyy-mm-dd')=to_char(starttime,'yyyy-mm-dd') order by starttime asc";
            string strSql = "select ID,USERISN,STARTTIME,ENDTIME,DUTYTYPE,DUTYMANAGER from SORTDUTY where SYSDATE> STARTTIME and SYSDATE<=ENDTIME order by STARTTIME asc";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 统计值班时长,查询duty_info
        /// </summary>
        /// <param name="starttime">统计开始时间</param>
        /// <param name="endtime">统计结束时间</param>
        /// <returns></returns>
        public DataSet getStatisticDutyHour(string starttime, string endtime)
        {
            //string strSql = "select userisn as 值班员,round(sum((endtime-starttime)*24)) as 值班时长 from sortduty  ";
            //strSql += " where (endtime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss')";
            //strSql += " GROUP BY  userisn";
            string strSql = "select userisn  as 值班员, round(sum((endtime-starttime)*24))  as 值班时长 from ";
            strSql += "(select userisn,starttime,case when endtime is null then sysdate else endtime end as endtime  from duty_info  ";
            strSql += " where (endtime>=to_date('" + starttime + "','yyyy-mm-dd hh24:mi:ss') or endtime is null) ";
            strSql += " and starttime<= to_date('" + endtime + "','yyyy-mm-dd hh24:mi:ss'))";
            strSql += " GROUP BY  userisn";
            return DBUtility.OracleHelper.Query(strSql);
        }
        public DataSet getSetFromMonth(string date)
        {
            string strSql = "select ID,USERISN,STARTTIME,ENDTIME,DUTYTYPE,DUTYMANAGER,to_char(STARTTIME,'dd') as d ,to_char(STARTTIME,'hh24')  as h from sortduty where to_char(starttime,'yyyy-mm')='" + date + "'";
            return DBUtility.OracleHelper.Query(strSql);
        }
        public DataSet getRecordByDate(string strStart, string strEnd)
        {
            DataSet ds = new DataSet();
            string strSql = "select ID,USERISN,STARTTIME,ENDTIME,DUTYTYPE,DUTYMANAGER,NODEID from sortduty";
            strSql += " where starttime >= " + strStart + " and starttime<" + strEnd + "";
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;
        }
    }
}
